package com.hualan.sfzyproject.mapper;

import com.hualan.sfzyproject.domain.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface CoursesMapper {



    int insert(Courses record);

    int insertSelective(Courses record);
    Courses selectById(Long id);
    List<Courses> selectByName(String name);
    List<Courses> searchCourses(String keyword);
    int batchDelete(List<Long> ids);
    int deleteByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(Courses record);

    int updateByPrimaryKey(Courses record);
    List<Courses> selectAll();
    List<Courses> selectAllCoursesAndRank(String type,Long typeId,Integer minPrice,Integer maxPrice);
    List<Courses> selectAllDescByStudyCount();
    //List<Courses> getCoursesByTypeId(String type,Long typeId);
//    List<Courses> selectAllDescByStudyCountAndTypeId(Long typeId);
//    List<Courses> selectAllDescByGoodAndTypeId(Long typeId);
//    List<Courses> selectAllOrderByPrice(Long typeId);
//    List<Courses> selectByPriceRange(Long typeId,Integer minPrice,Integer maxPrice);
    //课程收藏
    boolean addCourseCollect(CourseCollection courseCollection);
    //课程收藏的查询
    String checkCourseCollect(Integer userId, Integer courseId);
    //课程收藏的删除
    Boolean deleteCourseCollect(Integer userId, Integer courseId);
    //根据课程id添加收藏
    Integer updateByCourseId(Integer courseId);
    //查询课件列表
    @Select("select * from course_materials where course_id=#{id}")
    List<CourseMaterials> findList(Integer id);
    //名师介绍
//    使用 CAST(...AS CHAR) 将 GROUP_CONCAT 的结果明确转换为字符串类型
//    这样返回的 area_names 就不会是 BLOB 类型了，而是正常的字符串
//    这样修改后，返回的结果应该就是正常的字符串而不是 BLOB 了。
    @Select("SELECT \n" +
            "    c.*,\n" +
            "    CAST(GROUP_CONCAT(DISTINCT a.area_name SEPARATOR ',') AS CHAR) as area_names\n" +
            "FROM \n" +
            "    consultants c\n" +
            "LEFT JOIN \n" +
            "    consultant_specialization_area csa ON c.consultant_id = csa.consultant_id\n" +
            "LEFT JOIN \n" +
            "    areas a ON csa.area_id = a.area_id\n" +
            "WHERE \n" +
            "    c.consultant_id = #{id}\n" +
            "GROUP BY \n" +
            "    c.consultant_id")
    List<Consultants> findIntroduce(Integer id);
    //课堂交流
    @Select("select cq.*,c.consultant_name from course_questions cq,consultants c where course_id=#{arg0} and cq.consultant_id =c.consultant_id ")
    List<CourseQuestions> findCommunication(Integer id);
    //课程评价
    @Select("SELECT cr.*, u.user_name\n" +
            "FROM course_reviews cr\n" +
            "LEFT OUTER JOIN users u ON cr.user_id = u.user_id\n" +
            "WHERE cr.course_id = #{arg0} order by review_time desc;")
    List<CourseReviews> findReviews(Integer courseId,Integer userId);
    //查看回复
    @Select("SELECT\n" +
            "    u.user_name,\n" +
            "       cc.reply_time,\n" +
            "       cc.reply_content\n" +
            "FROM course_communication cc\n" +
            "LEFT JOIN course_questions cq ON cc.question_id = cq.question_id AND cq.course_id = #{arg1}\n" +
            "LEFT JOIN users u ON cc.user_id = u.user_id\n" +
            "WHERE cc.question_id = #{arg0} order by reply_time desc")
    List<CourseCommunication> findReply(Integer questionId, Integer courseId);
    //查看课程是否购买
    @Select("select * from course_purchase_record where user_id = #{arg1} and course_id = #{arg0}")
    String checkBuy(Integer courseId, Integer userId);
    //购买课程
    @Select("insert into course_purchase_record(user_id,course_id) values(#{userId},#{courseId})")
    Integer buyCourse(CoursePurchaseRecord coursePurchaseRecord);
    //改变收藏中的课程购买状态
    @Update("update course_collection set payment_state = 1 where user_id = #{userId} and course_id = #{courseId}")
    Integer updateState(CoursePurchaseRecord coursePurchaseRecord);
    //改变课程中的购买量
    @Update("update courses set purchase_volume = courses.purchase_volume + 1 where course_id = #{courseId}")
    Integer updatePurchaseVolume(CoursePurchaseRecord coursePurchaseRecord);
    //收藏查询已支付课程
    @Select("select c.* from courses c, course_collection cc where c.course_id=cc.course_id and cc.user_id = #{userId} and cc.payment_state = 1")
    List<Courses> findPayCourse(Integer userId);
    //收藏查询未支付课程
    @Select("select c.* from courses c, course_collection cc where c.course_id=cc.course_id and cc.user_id = #{userId} and cc.payment_state = 0")
    List<Courses> findNoPayCourse(Integer userId);
    //添加课程评价
    @Insert("insert into course_reviews(user_id,course_id,course_rating,review_content,review_time) values(#{userId},#{courseId},#{courseRating},#{reviewContent},#{reviewTime})")
    Boolean addReview(CourseReviews courseReviews);
    //添加课程里课程评价数量
    @Update("update courses set review_count = review_count + 1 where course_id = #{courseId}")
    Boolean updateReviewCount(CourseReviews courseReviews);
    //查询课程交流问题
    @Select("SELECT\n" +
            "    cq.question_id,\n" +
            "    cq.question_title,\n" +
            "    c.consultant_name,\n" +
            "    cq.publish_time,\n" +
            "    COUNT(cc.communication_id) as reply_count\n" +
            "FROM\n" +
            "    course_questions cq\n" +
            "    LEFT JOIN consultants c ON cq.consultant_id = c.consultant_id\n" +
            "    LEFT JOIN course_communication cc ON cq.question_id = cc.question_id\n" +
            "WHERE\n" +
            "    cq.course_id = #{arg0} -- 这里填入具体的课程ID\n" +
            "GROUP BY\n" +
            "    cq.question_id, cq.question_title, c.consultant_name\n" +
            "ORDER BY\n" +
            "    cq.question_id DESC")
    List<CommunicationVO> findQuestion(Integer courseId);
    //添加回复
    @Insert("insert into course_communication(question_id,user_id,reply_content,reply_time) values(#{questionId},#{userId},#{replyContent},now())")
    Boolean addReply(CourseCommunication courseCommunication);

}
